package org.sql.jdbc.dao;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.sql.jdbc.constants.DbTypeEnum;
import org.sql.jdbc.param.Operator;

/** 
 * jdbc查询操作层
 *
 * <pre> 
 * 构建组：e-jdbc
 * 作者：eddy
 * 邮箱：1546077710@qq.com
 * 日期：2017年4月23日-下午9:49:04
 * 版权：E源时代
 * </pre>
 */
public class JdbcBaseQueryDao {
	
	/***
	 * 数据库连接
	 */
	private Connection conn;
	
	public Connection getConn(){
		return conn;
	}
	
	private String dbType = DbTypeEnum.MYSQL.code();
	
	public String getDbType() {
		return dbType;
	}

	public void setDbType(String dbType) {
		this.dbType = dbType;
	}

	public void setConn(Connection conn){
		this.conn = conn;
		
		try {
			DatabaseMetaData daMe = conn.getMetaData();
			String ataName = daMe.getDatabaseProductName();
			dbType = DbTypeEnum.formCode(ataName).code();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 预编译SQL语句对象
	 * */
	private PreparedStatement pstmt;
	
	/**
	 * 结果集
	 * */
	private ResultSet rs;
	
	/**
	 * 构造连接
	 */
	public JdbcBaseQueryDao(Connection conn){
		setConn(conn);
	}

	/**
	 * 分页查询
	 *
	 * @param strSql	操作SQL
	 * @param obj		操作对象
	 * @param params	参数集合
	 * @param start		页码
	 * @param limit		每页显示记录数
	 * @return
	 * @throws Exception 
	 */
	public List<?> queryListByPage(String strSql,Object obj,List<Object> params,int start,int limit) throws Exception{
		if(null == strSql || "".equals(strSql) || 0 == strSql.length()){
			throw new Exception("SQL语句为空！");
		}
		StringBuilder querySql = null;
		try {
			// 如果是mysql
			if (dbType.equals(DbTypeEnum.MYSQL.code())) {
				querySql = new StringBuilder(strSql);
				querySql.append(" limit ");
				querySql.append(start);
				querySql.append(",");
				querySql.append(limit);
			}else if (dbType.equals(DbTypeEnum.ORACLE.code())) {
				int startIndex = 0;
				int endIndex = 0;
				startIndex = start + 1;
				endIndex = start + limit;
				querySql = new StringBuilder();
				querySql.append("SELECT * FROM (");
				querySql.append("SELECT ROWNUM AS RONUM,TAB.* FROM (");
				querySql.append(strSql);
				querySql.append(") TAB) WHERE RONUM BETWEEN ");
				querySql.append(startIndex + " AND " + endIndex);
			}else{
				
			}
			
			pstmt = conn.prepareStatement(querySql.toString());
			if (null != params && params.size() > 0) {
				for (int i = 1; i <= params.size(); i++) {
					pstmt.setObject(i, params.get(i - 1));
				}
			}
			rs = pstmt.executeQuery();
			List<?> list = Operator.toList(obj, rs);
			return list;
		} catch (SQLException e) {
			throw new Exception("操作数据异常:" + e.getMessage(), e);
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
				if (null != pstmt) {
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
	}
	
	/**
	 * 分页查询
	 *
	 * @param strSql	操作SQL
	 * @param obj		操作对象
	 * @param params	参数集合
	 * @param start		页码
	 * @param limit		每页显示记录数
	 * @return
	 * @throws Exception 
	 */
	public List<?> queryListByPage(String strSql,Class<?> obj,List<Object> params,int start,int limit) throws Exception{
		if(null == strSql || "".equals(strSql) || 0 == strSql.length()){
			throw new Exception("SQL语句为空！");
		}
		StringBuilder querySql = null;
		try {
			// 如果是mysql
			if (dbType.equals(DbTypeEnum.MYSQL.code())) {
				querySql = new StringBuilder(strSql);
				querySql.append(" limit ");
				querySql.append(start);
				querySql.append(",");
				querySql.append(limit);
			}else if (dbType.equals(DbTypeEnum.ORACLE.code())) {
				int startIndex = 0;
				int endIndex = 0;
				startIndex = start + 1;
				endIndex = start + limit;
				querySql = new StringBuilder();
				querySql.append("SELECT * FROM (");
				querySql.append("SELECT ROWNUM AS RONUM,TAB.* FROM (");
				querySql.append(strSql);
				querySql.append(") TAB) WHERE RONUM BETWEEN ");
				querySql.append(startIndex + " AND " + endIndex);
			}else{
				
			}
			
			pstmt = conn.prepareStatement(querySql.toString());
			if (null != params && params.size() > 0) {
				for (int i = 1; i <= params.size(); i++) {
					pstmt.setObject(i, params.get(i - 1));
				}
			}
			rs = pstmt.executeQuery();
			List<?> list = Operator.toList(obj, rs);
			return list;
		} catch (SQLException e) {
			throw new Exception("操作数据异常:" + e.getMessage(), e);
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
				if (null != pstmt) {
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
	}
	
	/**
	 * 集合查询
	 *
	 * @param strSql	操作SQL
	 * @param obj		操作对象
	 * @param params	参数集合
	 * @return
	 * @throws Exception 
	 */
	public List<?> queryList(String strSql,Object obj,List<Object> params) throws Exception{
		if(null == strSql || "".equals(strSql) || 0 == strSql.length()){
			throw new Exception("SQL语句为空！");
		}
		try {
			pstmt = conn.prepareStatement(strSql);
			if(null != params && params.size()>0){
				for(int i=1;i<=params.size();i++){
					pstmt.setObject(i,params.get(i-1));
				}
			}
			rs = pstmt.executeQuery();
			List<?> list = Operator.toList(obj, rs);
			return list;
		} catch (SQLException e) {
			throw new Exception("操作数据异常:"+e.getMessage(),e);
		} finally {
			try {
				if(null != rs){
					rs.close();
				}
				if(null != pstmt){
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
	}
	
	/**
	 * TODO方法名称描述
	 *
	 * @param strSql	操作SQL
	 * @param obj		操作对象
	 * @param params	参数集合
	 * @return
	 * @throws Exception 
	 */
	public List<?> queryList(String strSql,Class<?> obj,List<Object> params) throws Exception{
		if(null == strSql || "".equals(strSql) || 0 == strSql.length()){
			throw new Exception("SQL语句为空！");
		}
		try {
			pstmt = conn.prepareStatement(strSql);
			if(null != params && params.size()>0){
				for(int i=1;i<=params.size();i++){
					pstmt.setObject(i,params.get(i-1));
				}
			}
			rs = pstmt.executeQuery();
			List<?> list = Operator.toList(obj, rs);
			return list;
		} catch (SQLException e) {
			throw new Exception("操作数据异常:"+e.getMessage(),e);
		} finally {
			try {
				if(null != rs){
					rs.close();
				}
				if(null != pstmt){
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
	}
	
	/**
	 * 查询字符串数组集合
	 *
	 * @param strSql	操作SQL
	 * @param params	参数集合
	 * @return
	 * @throws Exception 
	 */
	public List<String[]> queryStrList(String strSql,List<Object> params) throws Exception{
		if(null == strSql || "".equals(strSql) || 0 == strSql.length()){
			throw new Exception("SQL语句为空！");
		}
		try {
			pstmt = conn.prepareStatement(strSql);
			if(null != params && params.size()>0){
				for(int i=1;i<=params.size();i++){
					pstmt.setObject(i,params.get(i-1));
				}
			}
			rs = pstmt.executeQuery();
			List<String[]> list = new ArrayList<String[]>();
			ResultSetMetaData rsd = rs.getMetaData();
			int columnNum = rsd.getColumnCount();
			while(rs.next()){
				String[] tmp = new String[columnNum];
				for(int i = 0 ;i < columnNum ;i ++){
					tmp[i] = rs.getString(i + 1);
				}
				list.add(tmp);
			}
			return list;
		} catch (SQLException e) {
			throw new Exception("操作数据异常:"+e.getMessage(),e);
		} finally {
			try {
				if(null != rs){
					rs.close();
				}
				if(null != pstmt){
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
	}
	
	/**
	 * 查询单个结果
	 *
	 * @param strSql	操作SQL
	 * @param params	参数集合
	 * @return
	 * @throws Exception 
	 */
	public Object getObject(String strSql, List<Object> params) throws Exception{
		if(null == strSql || "".equals(strSql) || 0 == strSql.length()){
			throw new Exception("SQL语句为空！");
		}
		try {
			pstmt = conn.prepareStatement(strSql);
			if(null != params && params.size()>0){
				for(int i=1;i<=params.size();i++){
					pstmt.setObject(i,params.get(i-1));
				}
			}
			rs = pstmt.executeQuery();
			Object obj = null ;
			if(rs.next()){
				obj = rs.getString(1);
			}
			return obj;
		} catch (SQLException e) {
			throw new Exception("操作数据异常:"+e.getMessage(),e);
		} finally {
			try {
				if(null != rs){
					rs.close();
				}
				if(null != pstmt){
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
	}
	
	/**
	 * 查询集合
	 *
	 * @param strSql	问号注入SQL语句
	 * @param obj		实体类
	 * @param params	实际参数数组
	 * @return
	 * @throws Exception 
	 */
	public List<?> queryList(String strSql, Object obj, Object[] params) throws Exception{
		if(null == strSql || "".equals(strSql) || 0 == strSql.length()){
			throw new Exception("SQL语句为空！");
		}
		try {
			pstmt = conn.prepareStatement(strSql);
			if(null != params ){
				for(int i=0;i<params.length;i++){
					pstmt.setObject(i+1,params[i]);
				}
			}
			rs = pstmt.executeQuery();
			List<?> list = Operator.toList(obj, rs);
			return list;
		} catch (SQLException e) {
			throw new Exception("操作数据异常:"+e.getMessage(),e);
		} finally {
			try {
				if(null != rs){
					rs.close();
				}
				if(null != pstmt){					
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
	}
	
	/**
	 * 查询集合
	 *
	 * @param strSql	问号注入SQL语句
	 * @param obj		实体类
	 * @param params	实际参数数组
	 * @return
	 * @throws Exception 
	 */
	public List<?> queryList(String strSql,Class<?> obj,Object[] params) throws Exception{
		if(null == strSql || "".equals(strSql) || 0 == strSql.length()){
			throw new Exception("SQL语句为空！");
		}
		try {
			pstmt = conn.prepareStatement(strSql);
			if(null != params ){
				for(int i=0;i<params.length;i++){
					pstmt.setObject(i+1,params[i]);
				}
			}
			rs = pstmt.executeQuery();
			List<?> list = Operator.toList(obj, rs);
			return list;
		} catch (SQLException e) {
			throw new Exception("操作数据异常:"+e.getMessage(),e);
		} finally {
			try {
				if(null != rs){
					rs.close();
				}
				if(null != pstmt){					
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
	}
	
	/**
	 * 查询集合
	 *
	 * @param strSql
	 * @param paramlist
	 * @return
	 * @throws Exception 
	 */
	public List<?> queryList(String strSql, List<Object> paramlist) throws Exception{
		if(null == strSql || "".equals(strSql) || 0 == strSql.length()){
			throw new Exception("SQL语句为空！");
		}
		try {
			pstmt = conn.prepareStatement(strSql);
			if(null == paramlist || 0 == paramlist.size()){
				rs = pstmt.executeQuery();
			}else{
				for(int i = 0; i < paramlist.size(); i ++){
					pstmt.setObject(i + 1, paramlist.get(i));
				}
				rs = pstmt.executeQuery();
			}
			List<?> list = Operator.toMapList(rs);
			return list;
		} catch (SQLException e) {
			throw new Exception("操作数据异常:"+e.getMessage(),e);
		} finally {
			try {
				if(null != rs){
					rs.close();
				}
				if(null != pstmt){					
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
	}
	
	/**
	 * 查询记录数
	 *
	 * @param strSql 	操作SQL
	 * @param params 	参数
	 * @return
	 * @throws Exception 
	 */
	public Integer queryCount(String strSql,List<Object> params) throws Exception{
		if(null == strSql || "".equals(strSql) || 0 == strSql.length()){
			throw new Exception("SQL语句为空！");
		}
		Integer rowCount = null;
		try {
			pstmt = conn.prepareStatement(strSql);
			if(null != params && params.size()>0){
				for(int i=1;i<=params.size();i++){
					pstmt.setObject(i,params.get(i-1));
				}
			}
			rs = pstmt.executeQuery();
			while(rs.next()){
				rowCount =rs.getInt(1);
			}
			return rowCount;
		} catch (SQLException e) {
			throw new Exception("操作数据异常:"+e.getMessage(),e);
		} finally {
			try {
				if(null != rs){
					rs.close();
				}
				if(null != pstmt){					
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
	}
	
	@Override
	protected void finalize() throws Throwable {
		super.finalize();
		conn.close();
	}
}
